﻿Imports System.Data.SqlClient

Public NotInheritable Class SQLExecute

    ''' <summary>SQL 実行時のタイムアウト（秒）</summary>
    Public Property Timeout As Integer = 30

    ''' <summary>SQL 実行時に出力された文字列</summary>
    Public Property InfoMessages As New List(Of String)

    ''' <summary>SQL を実行する</summary>
    Public Function ExecuteSQL(ByVal script As String, ByVal connect As String, ByVal needRes As Boolean) As DataSet
        Dim result As New DataSet
        Using sqlCon As New SqlConnection(connect)
            Try
                sqlCon.Open()
                AddHandler sqlCon.InfoMessage, AddressOf ConnectionInfoMessage

                Dim sqlCom As New SqlCommand(script, sqlCon) With {
                    .CommandTimeout = Me.Timeout
                }
                If needRes Then
                    Dim dtAdapter As New SqlDataAdapter With {
                        .SelectCommand = sqlCom,
                        .MissingSchemaAction = MissingSchemaAction.AddWithKey
                    }
                    dtAdapter.Fill(result)
                Else
                    sqlCom.ExecuteNonQuery()
                End If

                RemoveHandler sqlCon.InfoMessage, AddressOf ConnectionInfoMessage
                sqlCon.Close()
            Catch ex As Exception
                Me.InfoMessages.Add(ex.Message)
            End Try
        End Using
        Return result
    End Function

    ''' <summary>SQL ログへ出力する</summary>
    ''' <param name="message">出力するメッセージ</param>
    ''' <param name="severity">重大度レベル</param>
    ''' <param name="state">状態番号</param>
    ''' <returns>正常終了で True</returns>
    ''' <remarks>sysadmin または ALTER TRACE 権限が必要</remarks>
    Public Function OutputSQLLog(ByVal message As String, ByVal severity As Integer, ByVal state As Integer, ByVal connect As String) As Boolean
        Dim result As Boolean = False
        Using sqlCon As New SqlConnection(connect)
            Try
                sqlCon.Open()

                Dim script = "RAISERROR(N'" + message + "', " + severity.ToString + ", " + state.ToString + ") WITH LOG"
                Dim sqlCom As New SqlCommand(script, sqlCon)
                sqlCom.ExecuteNonQuery()

                sqlCon.Close()
                result = True
            Catch ex As Exception

            End Try
        End Using
        Return result
    End Function

    ''' <summary>実行結果メッセージを Dataset に変換</summary>
    Public Function ConvertMessageToDataset(ByVal messages As List(Of String)) As DataSet
        Dim result = CreateMessageDataset()
        For Each mes In messages
            Dim newRow = result.Tables(0).NewRow
            newRow(0) = mes
            result.Tables(0).Rows.Add(newRow)
        Next
        Return result
    End Function

    ''' <summary>SQL 実行時に出力された文字列を抽出する</summary>
    Private Sub ConnectionInfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
        Me.InfoMessages.Add(e.Message)
    End Sub

    ''' <summary>出力結果用 Dataset テーブルの作成</summary>
    Private Function CreateMessageDataset() As DataSet
        Dim result As New DataSet
        result.Tables.Add(New DataTable(My.Resources.MessageTableName))
        result.Tables(0).Columns.Add(My.Resources.MessageTableColumnName, GetType(String))
        Return result
    End Function


End Class
